package backend.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedList;

import logs.LogFactory;

import org.apache.log4j.Logger;

import backend.Course;
import backend.Session;
import backend.Util;
import backend.exceptions.DatabaseErrorException;
import backend.exceptions.ElementNotFoundException;

public class DBHandler {
  private static final Logger logger = LogFactory.getLog(DBHandler.class);
  public static final Cell[] NO_PARAMS = new Cell[0];
  
  public static Cell[] singleParam(final Cell c) {
    return new Cell[] { c };
  }
  
  /*-------------- QUERY HELPERS ---------------*/
  public static <T> Collection<T> selectSome(final Connection c, final String tableName, final Cell[] params,
      final Maker<T> maker) {
    return selectSome(c, "*", tableName, params, maker);
  }
  
  public static <T> Collection<T> selectSome(final Connection c, final String columnNames,
      final String tableName, final Cell[] params, final Maker<T> maker) {
    final Collection<T> $ = new LinkedList<T>();
    PreparedStatement stmt = null;
    ResultSet result = null;
    try {
      stmt = makeSelectQuery(c, columnNames, tableName, params);
      result = stmt.executeQuery();
      while (result.next())
        $.add(maker.make(result));
    } catch (final SQLException e) {
      throw new DatabaseErrorException(e.getMessage());
    } finally {
      Util.tryClose(result, stmt);
    }
    return $;
  }
  
  public static <T> T selectElement(final Connection c, final String tableName, final Cell[] params,
      final Maker<T> maker) throws ElementNotFoundException {
    return selectElement(c, "*", tableName, params, maker);
  }
  
  public static <T> T selectElement(final Connection c, final String colName, final String tableName,
      final Cell[] params, final Maker<T> maker) throws ElementNotFoundException {
    PreparedStatement stmt = null;
    ResultSet result = null;
    try {
      stmt = makeSelectQuery(c, colName, tableName, params);
      result = stmt.executeQuery();
      if (result.next())
        return maker.make(result);
    } catch (final SQLException e) {
      throw new DatabaseErrorException(e.getMessage());
    } finally {
      Util.tryClose(result, stmt);
    }
    throw new ElementNotFoundException();
  }
  
  public static long insert(final Connection c, final String tableName, final Cell[] params) {
    PreparedStatement stmt = null;
    try {
      stmt = makeInsertQuery(c, tableName, params);
      return stmt.executeUpdate();
    } catch (final SQLException e) {
      throw new DatabaseErrorException(e.getMessage());
    } finally {
      Util.tryClose(stmt);
    }
  }
  
  public static int delete(final Connection c, final String tableName, final Cell[] params) {
    PreparedStatement stmt = null;
    try {
      stmt = makeDeleteQuery(c, tableName, params);
      return stmt.executeUpdate();
    } catch (final SQLException e) {
      throw new DatabaseErrorException(e.getMessage());
    } finally {
      Util.tryClose(stmt);
    }
  }
  
  public static void update(final Connection c, final String tableName, final Cell[] params) {
    PreparedStatement stmt = null;
    try {
      stmt = makeUpdateQuery(c, tableName, params);
      if (0 == stmt.executeUpdate())
        throw new DatabaseErrorException("Updating table " + tableName + " failed");
    } catch (final SQLException e) {
      throw new DatabaseErrorException(e.getMessage());
    } finally {
      Util.tryClose(stmt);
    }
  }
  
  /*---------------- QUERY MAKERS --------------------*/
  private static PreparedStatement makeSelectQuery(final Connection c, final String colName,
      final String tableName, final Cell[] params) throws SQLException {
    final StringBuilder sb = new StringBuilder("SELECT ").append(colName).append(" FROM ").append(tableName);
    // set where if there any parameters for query
    if (params.length > 0) {
      sb.append(" WHERE ");
      // add until last
      sb.append(params[0].col.name).append("=?");
      for (int i = 1; i < params.length; ++i)
        sb.append(" AND ").append(params[i].col.name).append("=?");
    }
    sb.append(';');
    final PreparedStatement $ = c.prepareStatement(sb.toString());
    fillPreparedStatement($, params);
    logger.debug($);
    return $;
  }
  
  private static PreparedStatement makeInsertQuery(final Connection c, final String tableName,
      final Cell[] params) throws SQLException {
    // prepare query
    if (params.length == 0)
      throw new IllegalArgumentException("params can't be empty");
    final StringBuilder sb = new StringBuilder("INSERT INTO ").append(tableName).append(" (");
    sb.append(params[0].col.name);
    for (int i = 1; i < params.length; ++i)
      sb.append(", ").append(params[i].col.name);
    sb.append(") VALUES (?");
    for (int i = 0; i < params.length - 1; ++i)
      sb.append(", ?");
    sb.append(");");
    // prepare statement
    PreparedStatement $ = null;
    $ = c.prepareStatement(sb.toString());
    fillPreparedStatement($, params);
    logger.debug($);
    return $;
  }
  
  private static PreparedStatement makeDeleteQuery(final Connection c, final String tableName,
      final Cell[] params) throws SQLException {
    // prepare query
    if (params.length == 0)
      throw new IllegalArgumentException("params can't be empty");
    final StringBuilder sb = new StringBuilder("DELETE FROM ").append(tableName).append(" WHERE ");
    sb.append(params[0].col.name + "=?");
    for (int i = 1; i < params.length; ++i)
      sb.append(" AND ").append(params[i].col.name).append("=?");
    sb.append(';');
    // prepare statement
    final PreparedStatement $ = c.prepareStatement(sb.toString());
    fillPreparedStatement($, params);
    logger.debug($);
    return $;
  }
  
  private static PreparedStatement makeUpdateQuery(final Connection c, final String tableName,
      final Cell[] params) throws SQLException {
    // prepare query
    if (params.length < 2)
      throw new IllegalArgumentException("params can't be empty");
    final StringBuilder sb = new StringBuilder("UPDATE ").append(tableName).append(" SET ");
    sb.append(params[0].col.name).append("=?");
    for (int i = 1; i < params.length - 1; ++i)
      sb.append(", ").append(params[i].col.name).append("=?");
    sb.append(" WHERE ").append(params[params.length - 1].col.name).append("=? ;");
    // prepare statement
    final PreparedStatement $ = c.prepareStatement(sb.toString());
    fillPreparedStatement($, params);
    logger.debug($);
    return $;
  }
  
  /*---------------- OTHER HELPER METHODS --------------------*/
  /**
   * Fills a prepared statement
   * 
   * @param args
   * @param stmt
   * @throws SQLException
   */
  private static void fillPreparedStatement(final PreparedStatement stmt, final Cell[] args)
      throws SQLException {
    for (int i = 0; i < args.length; ++i)
      if (args[i].val == null)
        stmt.setNull(i + 1, args[i].col.intType);
      else
        stmt.setObject(i + 1, args[i].val, args[i].col.intType);
  }
  
  public static <T> Collection<T> selectDistinct(final Connection c, final String distinctColumn,
      final String tableName, final Cell[] params, final Maker<T> maker) {
    return selectSome(c, "DISTINCT(" + distinctColumn + ")", tableName, params, maker);
  }
  
  static Iterable<Course> selectAvailable(final Connection c, final String username) {
    logger.debug("selectAvailable: " + username);
    Statement stmt = null;
    PreparedStatement pStmt = null;
    ResultSet rs = null;
    final Collection<Course> $ = new LinkedList<Course>();
    final Collection<Course> myCourses = new LinkedList<Course>();
    try {
      stmt = c.createStatement();
      final String query = "SELECT courses.* FROM courses, capacities"
          + " WHERE courses.courseName = capacities.courseName AND capacities.space > 0;";
      logger.debug(query);
      rs = stmt.executeQuery(query);
      while (rs.next())
        $.add(Makers.makeCourseDeep(rs));
    } catch (final SQLException e) {
      logger.error("error in selectAvailable, 1st filter", e);
      return null;
    } finally {
      Util.tryClose(rs, stmt);
    }
    if ($.isEmpty())
      return $;
    // $ now contains all not full courses.
    logger.debug("1st filter");
    try {
      pStmt = c.prepareStatement("SELECT courseName, groupNum FROM registrations WHERE username=?;");
      pStmt.setString(1, username);
      logger.debug(pStmt);
      rs = pStmt.executeQuery();
      while (rs.next())
        myCourses.add(new Course(rs.getString(1), null, null, null, null, null, null, 0, null, null, rs
            .getInt(2), null));
    } catch (final SQLException e) {
      logger.error("error in selectAvailable, 2nd filter", e);
      return null;
    } finally {
      Util.tryClose(rs, pStmt);
    }
    if (myCourses.isEmpty())
      return $;
    $.removeAll(myCourses);
    if ($.isEmpty())
      return $;
    // $ now contains all not full courses that we're not registered to.
    logger.debug("2nd filter");
    final Collection<Integer> myGroups = new LinkedList<Integer>();
    for (final Course course : myCourses) {
      final Integer group = Integer.valueOf(course.groupNum);
      if (!myGroups.contains(group))
        myGroups.add(group);
    }
    if (myGroups.size() > 2) {
      // we're already registered to courses from 3 groups, so we can only
      // register to a course from one of those groups.
      final Iterator<Course> iter = $.iterator();
      while (iter.hasNext()) {
        final Course course = iter.next();
        if (!myGroups.contains(Integer.valueOf(course.groupNum)))
          iter.remove();
      }
      if ($.isEmpty())
        return $;
    }
    // $ now contains all the available courses, except maybe overlaps.
    logger.debug("3rd filter");
    final Collection<Session> mySessions = new LinkedList<Session>();
    for (final Course myCourse : myCourses)
      try {
        mySessions.addAll(getAllSessions(c, ORM.courseNameToId(c, myCourse.courseName)));
      } catch (final ElementNotFoundException e) {
        logger.error("error in selectAvailable, 4th filter", e);
        return null;
      }
    final Iterator<Course> iter = $.iterator();
    while (iter.hasNext()) {
      final Course course = iter.next();
      if (ORM.overlaps(getAllSessions(c, course.courseId), mySessions))
        iter.remove();
    }
    logger.debug("4th filter");
    return $;
  }
  
  private static Collection<Session> getAllSessions(final Connection c, final String courseId) {
    return DBHandler.selectSome(c, "sessions",
        singleParam(new Cell(Tables.getTable("sessions").getColumn("courseId"), courseId)),
        Makers.SESSION_MAKER);
  }
}
